import xlrd
from datetime import date,datetime

import csv

f = open('cmc.csv', 'w', encoding='utf-8-sig')
csv_write = csv.writer(f, lineterminator='\n')

list_context = []

col_start = -1
col_skip = -1

def write_csv(sheet, list_csv, col_start, col_skip, skip_head):

    row_num = sheet.nrows
    col_num = sheet.ncols
    #print (sheet.name,sheet.nrows,sheet.ncols)

    i = 0
    j = 0
    list_temp = []
    for i in range (0, row_num):
        for j in range(col_start, col_num):
            if j != col_skip:
                cell_value = sheet.cell_value(i, j)
                ctype =  sheet.cell(i, j).ctype
                if ctype == 1:  #单元格返回为String类型去掉换行等特殊字符
                   cell_value = cell_value.replace('\r', '').replace('\n', '').replace('\t', '')
                head_value = sheet.cell_value(i, col_start)
                if skip_head == 1 and (head_value == '' or  head_value == "样本编号"): #是否跳过首行，且首列为空或者'样本编号'
                    continue;
                list_temp.append(cell_value)
        if list_temp:
           list_csv.append(list_temp)
        #print(list_temp)
        list_temp = []

def get_rule(sheet, list):
    i = 0
    j = 0
    for i in range (0, sheet.nrows):
        for j in range(0, sheet.ncols):
            value = sheet.cell_value(i, j)
            ctype = sheet.cell(i, j).ctype
            if ctype == 1:  # 单元格返回为String类型去掉换行等特殊字符
                value = value.replace('\r', '').replace('\n', '').replace('\t', '')
            if value == "样本编号":
                list.append(j)
                value1 = sheet.cell_value(i, j+1)
                if value1 == "模式":
                    list.append(j+1)
                break
    list_size =  len(list)
    if list_size < 2:
        list.append(-1)


def read_excel():
    # 打开文件
    workbook = xlrd.open_workbook(r'chat.xlsx')


    list_start_skip = []
    sheet = workbook.sheet_by_index(1)
    get_rule(sheet, list_start_skip)
    print(list_start_skip)
    write_csv(sheet, list_context, list_start_skip[0], list_start_skip[1], 0)
    list_start_skip = []

    sheet = workbook.sheet_by_index(2)
    get_rule(sheet, list_start_skip)
    print(list_start_skip)
    write_csv(sheet, list_context, list_start_skip[0], list_start_skip[1], 1)
    list_start_skip = []

    sheet = workbook.sheet_by_index(3)
    get_rule(sheet, list_start_skip)
    print(list_start_skip)
    write_csv(sheet, list_context, list_start_skip[0], list_start_skip[1], 1)
    list_start_skip = []

    sheet = workbook.sheet_by_index(4)
    get_rule(sheet, list_start_skip)
    print(list_start_skip)
    write_csv(sheet, list_context, list_start_skip[0], list_start_skip[1], 1)
    list_start_skip = []

    #写CSV文件
    for row in list_context:
     # print(row)
      csv_write.writerow(row)


if __name__ == '__main__':
    # 读取Excel
    read_excel();
    print ('注册数据写入成功！')